package cn.tang.crm.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Queue;
import java.util.concurrent.ConcurrentLinkedQueue;

import javax.sql.DataSource;
import org.apache.log4j.Logger;
import cn.tang.crm.config.Configure;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public class DBConnectionPool {
	private static final Logger logger = Logger.getLogger(DBConnectionPool.class);
	public DataSource datasource = null;
	public static Queue<Connection> connectionPool = new ConcurrentLinkedQueue<Connection>();

	private static DBConnectionPool instance = new DBConnectionPool();

	private DBConnectionPool() {
		try {
			initDataSource();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static DBConnectionPool getInstance() {
		return instance;
	}

	public void returnResource(Connection conn, Statement stat) {
		if (stat != null)
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		returnConnection(conn);
	}

	public void returnConnection(Connection connection) {
		if (connection != null)
			connectionPool.add(connection);
	}

	public Connection getConnection() {
		Connection connection = connectionPool.poll();
		if (connection == null) {
			if (datasource != null) {
				Connection conn = null;
				try {
					conn = datasource.getConnection();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return conn;
			}
		} else {
			try {
				if (!connection.isClosed())
					return connection;
				else
					return getConnection();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public void initDataSource() throws SQLException {
		Configure configure = Configure.getInstance();
		MysqlDataSource dataSource = new MysqlDataSource();
		dataSource.setUrl(configure.getDbURL());
		dataSource.setUser(configure.getDbUserName());
		dataSource.setPassword(configure.getDbPassword());
		datasource = dataSource;
	}

	public void batchUpdate(List<String> sqlList) throws SQLException {
		Connection connection = getConnection();
		connection.setAutoCommit(false);
		Statement stmt = connection.createStatement();
		for (String sql : sqlList) {
			stmt.addBatch(sql);
		}
		try {
			stmt.executeBatch();
			connection.commit();
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e.getMessage());
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			throw e;
		} finally {
			returnResource(connection, stmt);
		}
	}

	public int executeUpdate(String sql){
		Connection connection = getConnection();
		Statement stmt = null;
		try {
			stmt = connection.createStatement();
			return stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			returnResource(connection, stmt);
		}
		return 0;
	}

	public void createDatabase() throws SQLException {
		Connection connection = datasource.getConnection();
		String sql = "create table if not exists asmm";
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (Exception ignore) {
				}
		}
	}

	public void createTables() throws SQLException {
		Connection connection = datasource.getConnection();
		String sql = "create table if not exists space_stats (" + "timestamp int, " + "metaspace_name varchar(50)," + "space_name varchar(50),"
				+ "member_id varchar(50) ," + "entity_count bigint default -1," + "put_count bigint default -1," + "take_count bigint default -1,"
				+ "get_count bigint default -1," + "miss_count bigint default -1," + "lock_count bigint default -1,"
				+ "primary key(timestamp,metaspace_name,space_name,member_id) )";
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (Exception ignore) {
				}
		}
	}
}
